﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using ComDevFx.Arch.Base.Persister.NH.Burrow.AppBlock.DAOBases;
using NHibernate;

namespace ComDevFx.Arch.Base.Persister
{
    public  class DaoBase<T> : GenericDAO<T>
    {
        /// <summary>
        /// Execute a  Hibernate query
        /// this method will automatically add "from Team " before the above query.
        /// </summary>
        /// <param name="queryString"></param>
        /// <returns></returns>
        /// <example>
        /// you can even skip the alias "tm" if you use "this" as the alias for the current entity
        /// <code>
        ///  dao.CreateQuery("where this.TeamType = :tt");	
        ///  dao.CreateQuery("order by this.CreationTime");	
        /// </code>
        /// this method will automatically add "from Team this " before the above query.
        /// </example>
        public virtual IList<T> Query(string queryString)
        {
            IQuery query = base.CreateQuery(queryString);
            return query.List<T>();
        }

        /// <summary>
        /// NHibernate's top query 
        /// </summary>
        /// <param name="queryString">nhibernate's query's syntx string</param>
        /// <param name="top">record count</param>
        /// <returns></returns>
        public virtual IList<T> Top(string queryString, int top)
        {
            IQuery query = base.CreateQuery(queryString).SetMaxResults(top);
            return query.List<T>();
        }

        /// <summary>
        /// Query result from named query in entity's mapping config
        /// </summary>
        /// <param name="queryName">configed named query</param>
        /// <param name="setStringDic">params</param>
        /// <returns></returns>
        public virtual IList<T> GetNamedQuery(string queryName, IDictionary<string, string> setStringDic )
        {
            var query =   Session.GetNamedQuery(queryName);
            foreach (var eachStringDic in setStringDic)
            {
                query.SetString(eachStringDic.Key, eachStringDic.Value);
            }

            if (DefaultCacheable)
            {
                query.SetCacheable(DefaultCacheable).SetCacheRegion(DefaultCacheRegion);
            }

            query.SetResultTransformer(
                new NHibernate.Transform.AliasToBeanConstructorResultTransformer(
                    typeof (T).GetConstructors()[0]));

            return query.List<T>();
        }

        /// <summary>
        /// Invoke a indicated stored procdure
        /// </summary>
        /// <param name="spName">stored procdure name</param>
        /// <param name="paraInfo">key-value param dictionary</param>
        /// <param name="paraAction">each param's dbtype and para direction</param>
        /// <param name="outParaValue">out pama's value after sp execute</param>
        /// <returns></returns>
        public virtual DataSet ExecuteStoredProcdure(string spName, IDictionary<string, object> paraInfo, 
            IDictionary<string, KeyValuePair<DbType, ParameterDirection>> paraAction,
            out IDictionary<string, object> outParaValue )
        {
            outParaValue = new Dictionary<string, object>();
            var connection = Session.Connection;
            var ds = new DataSet();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = spName;
                command.CommandType = CommandType.StoredProcedure;
                Session.Transaction.Enlist(command);    //用于事务,如果在一个NHibernate本地事务中执行存储过程,
                                                                            //需用这个事物初始化一下IDbCommand ,从而把这个IDbCommand 对象加入到事务处理中
                foreach (var pi in paraInfo)
                {
                    IDbDataParameter parameter = command.CreateParameter();
                    parameter.ParameterName = pi.Key;
                    parameter.DbType = paraAction[pi.Key].Key;
                    parameter.Direction = paraAction[pi.Key].Value;
                    parameter.Value = pi.Value;
                    command.Parameters.Add(parameter);
                }
          
                //exectue
                using (IDataReader reader = command.ExecuteReader())
                {
                    var result = new DataTable();
                    //result.Load(reader);//此方法亦可
                    DataTable schemaTable = reader.GetSchemaTable();
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        string columnName = schemaTable.Rows[i][0].ToString();
                        result.Columns.Add(columnName);
                    }
                    while (reader.Read())
                    {
                        int fieldCount = reader.FieldCount;
                        var values = new Object[fieldCount];
                        for (int i = 0; i < fieldCount; i++)
                        {
                            values[i] = reader.GetValue(i);
                        }
                        result.Rows.Add(values);
                    }
                    ds.Tables.Add(result);
                }
                /*
                 * <<<The reason why out params has no value while reader is not closed as follows:>>>
                 * This is due to the "by design" nature of DataReaders. Any parameters marked as ParameterDirection.
                 * Output won't be "filled" until the DataReader has been closed. While still open, all Output parameters 
                 * will more than likely just come back null.
                 * http://stackoverflow.com/questions/65662/output-parameters-not-readable-when-used-with-a-datareader
                 */
                var outDics = paraAction.Where(kp => kp.Value.Value == ParameterDirection.Output);
                foreach (var keyValuePair in outDics)
                {
                    var dbDataParameter = command.Parameters[keyValuePair.Key] as IDbDataParameter;
                    if (dbDataParameter != null)
                        outParaValue.Add(keyValuePair.Key, dbDataParameter.Value);
                }
            }

            return ds;
        }

        /// <summary>
        /// NHibernate inner pager function
        /// </summary>
        /// <param name="where">where condition list. key:column name, value: column value</param>
        /// <param name="orderBy">order list. key: order column's name, value: order column's value</param>
        /// <param name="like">like condition.</param>
        /// <param name="pageSize">thie size of each page</param>
        /// <param name="curPageIndex">current pager index</param>
        /// <param name="recordCount">total records</param>
        /// <param name="totalPages">total pages</param>
        /// <returns>coditioned result list</returns>
        public virtual IList<T> GetPagerList(IDictionary<String, Object> where, IDictionary<String, Boolean> orderBy, IDictionary<String, Object> like,
            int pageSize, int curPageIndex, out int recordCount, out int totalPages)
        {
            ICriteria list = base.CreateCriteria();

            #region Condition Search

            if (where != null && where.Count > 0)
            {
                foreach (var item in where)
                {
                    list.Add(NHibernate.Criterion.Restrictions.Eq(item.Key, item.Value));
                }
            }

            #endregion

            #region Vague Search
            if (like != null && like.Count > 0)
            {
                foreach (var item in like)
                {
                    list.Add(NHibernate.Criterion.Restrictions.Like(item.Key, item.Value));
                }
            }
            #endregion

            #region Sort
            if (orderBy != null && orderBy.Count > 0)
            {
                foreach (var item in orderBy)
                {
                    if (item.Value)//true-asc,false-desc
                        list.AddOrder(NHibernate.Criterion.Order.Asc(item.Key));
                    else
                        list.AddOrder(NHibernate.Criterion.Order.Desc(item.Key));

                }
            }
            #endregion

            if (DefaultCacheable)
            {
                list.SetCacheable(DefaultCacheable).SetCacheRegion(DefaultCacheRegion);
            }

            recordCount = Count(list);  //Convert.ToInt32(list.List().Count);
            list.SetFirstResult((curPageIndex - 1) * pageSize)
                    .SetMaxResults(pageSize);

            totalPages = recordCount % pageSize == 0 ? (recordCount / pageSize) : (recordCount / pageSize + 1);

            return list.List<T>();
        }

        /// <summary>
        /// NHibernate inner pager function, recommend
        /// </summary>
        /// <param name="tableName">db table name</param>
        /// <param name="columns">query columns. all: *, others: Id, Name, ....</param>
        /// <param name="whereCondition">condition without "where" word</param>
        /// <param name="orderColumn">which db cloumn to be ordered</param>
        /// <param name="isDesc">desc or asc</param>
        /// <param name="pageSize">thie size of each page</param>
        /// <param name="currentPageIndex">current pager index</param>
        /// <param name="recordCount">total records</param>
        /// <param name="totalPages">total pages</param>
        /// <returns>coditioned result list</returns>
        public virtual IList<T> GetPagerList(string tableName, string columns, string whereCondition, string orderColumn, bool isDesc,
            int pageSize, int currentPageIndex, out int recordCount, out int totalPages)
        {
            var sbSql = new StringBuilder();
            sbSql.Append(" select ");
            sbSql.Append(columns);
            sbSql.Append(" from ");
            sbSql.Append(tableName);
            sbSql.Append(" with(nolock) ");

            sbSql.Append(string.IsNullOrEmpty(whereCondition) ? " " : (" where " + whereCondition));
            sbSql.Append(string.IsNullOrEmpty(orderColumn) ? "  " : ((" order by " + orderColumn) + (isDesc ? " DESC " : " ASC ")));

            //execute sql
            IQuery list = Session.CreateSQLQuery(sbSql.ToString())
                .AddEntity(typeof(T));

            if (DefaultCacheable)
            {
                list.SetCacheable(DefaultCacheable).SetCacheRegion(DefaultCacheRegion);
            }

            recordCount = list.List().Count;
            list.SetFirstResult((currentPageIndex - 1) * pageSize)
                    .SetMaxResults(pageSize);

            totalPages = recordCount % pageSize == 0 ? (recordCount / pageSize) : (recordCount / pageSize + 1);

            return list.List<T>();
        }
    }

    public enum GetType
    {
        Get,
        Load
    }
}
